package com.simple.common.utils.ExcelUtil;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.map.multi.ListValueMap;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    public static <T> List<T> readExcel(File file, Class<T> clazz, Integer headLineNum) throws Exception {
        ExcelListener excelListener = new ExcelListener(headLineNum);
        InputStream fileInputStream = new FileInputStream(file);
        ExcelReader excelReader = getReader(fileInputStream, file.getName(), clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        excelReader.read(readSheetList.get(0));
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }

    public static <T> List<T> readExcel(MultipartFile multipartFile, Class<T> clazz, Integer headLineNum) throws Exception {
        ExcelListener excelListener = new ExcelListener(headLineNum);
        ExcelReader excelReader = getReader(multipartFile.getInputStream(), multipartFile.getOriginalFilename(), clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        excelReader.read(readSheetList.get(0));
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }

    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     *
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        excelReader.read(readSheetList.get(0));
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }


    /**
     * 读取多个sheet的并且sheet的表头不一样Excel（clazz字段要涵盖多个sheet的表头）
     *
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readAllSheetExcel(InputStream excelInputStream, String fileName, Class<T> clazz,Integer headLineNum) {
        ExcelListener excelListener = new ExcelListener(headLineNum);
        ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet item : readSheetList){
            excelReader.read(item);
        }
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }


    /**
     * 读取多个sheet，用此方法
     * @param multipartFile
     * @param headLineNum
     * @param clazz
     * @return
     * @param <T>
     * @throws Exception
     */
    public static <T> List<T> readExcelAllSheet(MultipartFile multipartFile,Class<T> clazz,Integer headLineNum) throws Exception {
        ExcelListener excelListener = new ExcelListener(headLineNum);
        ExcelReader excelReader = getReader(multipartFile.getInputStream(), multipartFile.getOriginalFilename(), clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet item : readSheetList){
            excelReader.read(item);
        }
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());

    }

    /**
     * 网页导出Excel,会删除本地临时文件
     *
     * @param response  HttpServletResponse
     * @param list      数据list
     * @param fileName  导出的文件名
     * @param sheetIndex 要写的sheet的位置
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, Integer sheetIndex, Class<T> clazz) {
        String filePath = fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            fileName = URLEncoder.encode(filePath, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).build();
            String sheetName = "sheet";
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex, sheetName + sheetIndex).build();
            excelWriter.write(list, writeSheet);

            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            file.delete();
        }
    }

    /**
     * 导出excel到指定目录,不会删除文件
     * @param list 数组
     * @param fileName 文件名
     * @param targetDirPath 保存目录
     */
    public static <T> String writeExcelToTargetDir(List<T> list, String fileName, String targetDirPath, Class<T> clazz) {
        String filePath = targetDirPath + File.separator + fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            ExcelWriter excelWriter = EasyExcel.write(filePath, clazz).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
            excelWriter.write(list, writeSheet);
            excelWriter.finish();
        }catch (Exception e){
            e.printStackTrace();
        }

        return filePath;
    }

    /**
     * 根据模板填充excel
     * @param fillDataList 填充数据实体类
     * @param storeFileWholePath 生成文档的全路径
     * @param templateFilWholePath 模板的全路径
     */
    //FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    public static void fillDataIntoExcelByTemplate(List<FillDataDTO> fillDataList, String storeFileWholePath, String templateFilWholePath) {
        //如果写进try里面请手动关闭流
        try(ExcelWriter excelWriter = EasyExcel.write(storeFileWholePath).withTemplate(templateFilWholePath).build()) {
            WriteSheet writeSheet;
            ListValueMap<String, Object> tableNameDataList;
            for (FillDataDTO fillDataDTO : fillDataList) {
                writeSheet = EasyExcel.writerSheet(fillDataDTO.getSheetIndex()).build();
                tableNameDataList = fillDataDTO.getTableNameDataList();
                for (String tableName : tableNameDataList.keySet()) {
                    excelWriter.fill(new FillWrapper(tableName, tableNameDataList.get(tableName)), writeSheet);
                }
                excelWriter.fill(fillDataDTO.getSimpleDataMap(), writeSheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 写多个sheet到指定目录
     * @param targetDirPath 指定目录
     * @param fileName 文件名
     * @param classEntityList key是实体类的class必须带excelProperty来创建表头,value是要填的实体类
     */
    public static void writeExcelWithDifferentSheetToTargetDir(String targetDirPath, String fileName, Map<Class, List<Object>> classEntityList) {
        String filePath = targetDirPath + File.separator + fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            WriteSheet writeSheet;
            ExcelWriter excelWriter = EasyExcel.write(filePath).build();
            int i = 1;
            List<Object> objects;
            for (Class clazz : classEntityList.keySet()) {
                writeSheet = EasyExcel.writerSheet(i, "sheet" + i).head(clazz).build();
                objects = classEntityList.get(clazz);
                excelWriter.write(objects, writeSheet);
                i++;
            }
            excelWriter.finish();
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 写excel到指定目录
     * @param targetDirPath 指定目录
     * @param fileName 文件名
     * @param dataList
     */
    public static void writeExcelWithDifferentSheetToTargetDir(String targetDirPath, String fileName, List<SheetNameDateDTO> dataList) {
        String filePath = targetDirPath + File.separator + fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            WriteSheet writeSheet;
            ExcelWriter excelWriter = EasyExcel.write(filePath).build();
            int i = 1;
            for (SheetNameDateDTO dto : dataList) {
                writeSheet = EasyExcel.writerSheet(i, dto.getSheetName()).head(dto.getClazz()).build();
                excelWriter.write(dto.getDataList(), writeSheet);
                i++;
            }
            excelWriter.finish();
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void writeExcelWithDifferentSheet(HttpServletResponse response, ListValueMap<Class, Object> classEntityList, String fileName) {
        OutputStream outputStream = getOutputStream(response, fileName);
        WriteSheet writeSheet;
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        int i = 1;
        List<Object> objects;
        for (Class clazz : classEntityList.keySet()) {
            writeSheet = EasyExcel.writerSheet(i, "sheet" + i).head(clazz).build();
            objects = classEntityList.get(clazz);
            excelWriter.write(objects, writeSheet);
            i++;
        }
        excelWriter.finish();
    }

    /**
     * 导出时生成OutputStream
     */
    //todo 导出时候创建本地文件，理论上要在完成后删除本地文件
    private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            fileName = new String(filePath.getBytes(), StandardCharsets.UTF_8);
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回ExcelReader 默认读取合并单元格数据
     *
     * @param clazz         实体类
     * @param excelListener
     */
    private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
        try {
            if (filename == null ||
                    (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
                return null;
            }
            ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).extraRead(CellExtraTypeEnum.MERGE).build();
            inputStream.close();
            return excelReader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * @description: 根据模版导出Excel入口（多个list数据）
     * @param templateFileName
     * @param exportFilePathAndName
     * @param staticSource
     * @param originDataMapList
     * @return void
     * @author susu
     */
    public static void doExportExcelMoreListByTemplate(String templateFileName,
                                                       String exportFilePathAndName,
                                                       Map<String, Object> staticSource,
                                                       Map<String, List<?>> originDataMapList) throws Exception{

        Map<String,List<Map<String, Object>>> transMap = new HashMap<>();
        originDataMapList.forEach((dataId,originDataList)->{
            List<Map<String, Object>> transDataList = MapObjectUtil.objListToMapList(originDataList);
            transMap.put(dataId,transDataList);
        });
        // 多个list类型数据
        List<DynamicDataMapping> dynamicDataMappingList = DynamicDataMapping.createMorDataList(transMap);
        // 导出
        ExcelTemplateProc.doExportExcelByTemplateProc(templateFileName,exportFilePathAndName,staticSource,dynamicDataMappingList);

    }

    /**
     * @description: 根据模版导出Excel入口（单个list数据）
     * @param templateFileName
     * @param exportFilePathAndName
     * @param staticDataMap
     * @param dataId
     * @param originDataList
     * @return void
     * @author susu
     */
    public static void doExportExcelOneListByTemplate(String templateFileName, String exportFilePathAndName,
                                                      Map<String, Object> staticDataMap,
                                                      String dataId,
                                                      List<?> originDataList) throws Exception{

        List<Map<String, Object>> exportDataList = MapObjectUtil.objListToMapList(originDataList);
        // 只有一个list数据
        List<DynamicDataMapping> dynamicDataMappingList = DynamicDataMapping.createOneDataList(dataId, exportDataList);
        // 导出
        ExcelTemplateProc.doExportExcelByTemplateProc(templateFileName,exportFilePathAndName,staticDataMap,dynamicDataMappingList);

    }



}

